# Copyright 2023 Observational Health Data Sciences and Informatics
#
# This file is part of DataQualityDashboard
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

#' Write JSON Results to SQL Table
#'
#' @param connectionDetails         A connectionDetails object for connecting to the CDM database
#' @param resultsDatabaseSchema     The fully qualified database name of the results schema
#' @param jsonFilePath              Path to the JSON results file generated using the execute function
#' @param writeTableName            Name of table in the database to write results to
#' @param cohortDefinitionId        If writing results for a single cohort this is the ID that will be appended to the table name
#'
#' @export

writeJsonResultsToTable <- function(connectionDetails,
                                    resultsDatabaseSchema,
                                    jsonFilePath,
                                    writeTableName = "dqdashboard_results",
                                    cohortDefinitionId = c()) {
  jsonData <- jsonlite::read_json(jsonFilePath)
  checkResults <- lapply(jsonData$CheckResults, function(cr) {
    cr[sapply(cr, is.null)] <- NA
    as.data.frame(cr)
  })
  df <- do.call(plyr::rbind.fill, checkResults)

  connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)
  on.exit(DatabaseConnector::disconnect(connection = connection))

  if (length(cohortDefinitionId > 0)) {
    tableName <- sprintf("%s.%s_%s", resultsDatabaseSchema, writeTableName, cohortDefinitionId)
  } else {
    tableName <- sprintf("%s.%s", resultsDatabaseSchema, writeTableName)
  }

  ParallelLogger::logInfo(sprintf("Writing results to table %s", tableName))

  if ("unitConceptId" %in% colnames(df)) {
    ddl <- SqlRender::loadRenderTranslateSql(
      sqlFilename = "result_table_ddl_concept.sql",
      packageName = "DataQualityDashboard",
      tableName = tableName,
      dbms = connectionDetails$dbms
    )
  } else if ("cdmFieldName" %in% colnames(df)) {
    ddl <- SqlRender::loadRenderTranslateSql(
      sqlFilename = "result_table_ddl_field.sql",
      packageName = "DataQualityDashboard",
      tableName = tableName,
      dbms = connectionDetails$dbms
    )
  } else {
    ddl <- SqlRender::loadRenderTranslateSql(
      sqlFilename = "result_table_ddl_table.sql",
      packageName = "DataQualityDashboard",
      tableName = tableName,
      dbms = connectionDetails$dbms
    )
  }

  DatabaseConnector::executeSql(connection = connection, sql = ddl, progressBar = TRUE)

  # convert column names to snake case, omitting the autogenerated rowname column
  # and the checkId column, which has no underscore in the results table DDL
  for (i in 1:ncol(df)) {
    if (colnames(df)[i] %in% c("X_row", "checkId")) {
      colnames(df)[i] <- tolower(colnames(df)[i])
    } else {
      colnames(df)[i] <- SqlRender::camelCaseToSnakeCase(colnames(df)[i])
    }
  }

  tryCatch(
    expr = {
      DatabaseConnector::insertTable(
        connection = connection, tableName = tableName, data = df,
        dropTableIfExists = FALSE, createTable = FALSE, tempTable = FALSE,
        progressBar = TRUE
      )
      ParallelLogger::logInfo("Finished writing table")
    },
    error = function(e) {
      ParallelLogger::logError(sprintf("Writing table failed: %s", e$message))
    }
  )

  # .writeResultsToTable(connectionDetails = connectionDetails,
  #                      resultsDatabaseSchema = resultsDatabaseSchema,
  #                      checkResults = df)
}


#' Write JSON Results to CSV file
#'
#' @param jsonPath    Path to the JSON results file generated using the execute function
#' @param csvPath     Path to the CSV output file
#' @param columns     (OPTIONAL) List of desired columns
#' @param delimiter   (OPTIONAL) CSV delimiter
#'
#' @export

writeJsonResultsToCsv <- function(jsonPath,
                                  csvPath,
                                  columns = c(
                                    "checkId", "failed", "passed",
                                    "isError", "notApplicable",
                                    "checkName", "checkDescription",
                                    "thresholdValue", "notesValue",
                                    "checkLevel", "category",
                                    "subcategory", "context",
                                    "checkLevel", "cdmTableName",
                                    "cdmFieldName", "conceptId",
                                    "unitConceptId", "numViolatedRows",
                                    "pctViolatedRows", "numDenominatorRows",
                                    "executionTime", "notApplicableReason",
                                    "error", "queryText"
                                  ),
                                  delimiter = ",") {
  tryCatch(
    expr = {
      ParallelLogger::logInfo(sprintf("Loading results from %s", jsonPath))
      jsonData <- jsonlite::read_json(jsonPath)
      checkResults <- lapply(jsonData$CheckResults, function(cr) {
        cr[sapply(cr, is.null)] <- NA
        as.data.frame(cr)
      })
      .writeResultsToCsv(
        checkResults = do.call(plyr::rbind.fill, checkResults),
        csvPath = csvPath,
        columns = columns,
        delimiter = delimiter
      )
    },
    error = function(e) {
      ParallelLogger::logError(sprintf("Writing to CSV file failed: %s", e$message))
    }
  )
}
